package core


class AssignmentOneController {

    def index() { }
	//************************question One*****************
	def  oneFind()
	{
		
		
		def p = Person.findByBirthDateLessThanEquals(min(birthDate))
				def  stb1 = new StringBuilder()
				
				def map1 = [ oneFind: p.each { stb1  << it.id + ' ' +it.firstName + ' ' + it.lastName + '\n'	} ]
				render(view: 'oneFind', model: map1)

		
		
	}
	
	def oneWhere()
	{
		def stb2 = new StringBuilder()	
		 def q1 = Person.where {  (birthDate) <= min(birthDate)    }
		 def oldest = q1.list()
		
		 def map1 = [ oneWhere: oldest.each { stb2 <<  "$it.firstName $it.lastName $it.birthDate  \n"	} ]
		 render(view: "oneWhere", model: map1)
	}
	
	
	def oneCriteria(){
		def stb3 = new StringBuilder()
	
	    def criteria = Person.createCriteria()
        def oldest = criteria.list{order('birthDate','asc')}
		def result = oldest[0]
		
 
		def map1 = [ oneCriteria: result ]
		render(view: "oneCriteria", model: map1)
 
		
}
//****************************************************************************************************	
//***********************Question Tow***************
	def towFind()
	{
		
		
	}
	
	def towWhere()
	{
		
		
	}
	
	def towCriteria()
	{
		
		
	}
//	****************************************************************************************************
//***************************Question three*********************************
	def threeFind()
	{
		def p = DocumentType.findByName('id%' || '%cret%')
		def  stb1 = new StringBuilder()
		
		
		def map3 = [ threeFind: p.each { stb1  << it.id	} ]
		render(view: 'threeFind', model: map3)
		
	}
	
	def threeWhere()
	{
		def str = new StringBuilder()
		def query = DocumentType.where {name =~ "id%" || name =~ "%cert%"}
		def result = query.list()
		def map3 = [ threeWhere: result ]
		render(view: 'threeWhere', model: map3)
		
		
       
    
		
		
	}
	
	def threeCriteria()
	{
		def str = new StringBuilder()
		def query = Person.createCriteria()
		def result = query.listDistinct {
			legalIdentifiers {
				documentType {
					or {
						ilike('name','cert%')
						ilike('name','id%')
					}
				}
			}

		}
		
		
		def map3 = [ threeCriteria: result ]
		render(view: 'threeCriteria', model: map3)
		
	}
//***************************************************************************************************
//********************Question four***************************
	def fourFind()
	{
		def str = new StringBuilder()
		def query = Person.findAllByLastNameLike('Ali',[sort:'birthDate',max:1,offset:0 ,order:'desc'])
		def finalResult = Person.findAllByBirthDateGreaterThan(query[0].birthDate)
		def map4 = [fourFind: finalResult.each{str <<  "$it.firstName $it.lastName $it.birthDate  \n"}]
		render(view: 'fourFind',model: map4)
		
	}	
	
	def fourWhere()
	{
		def str = new StringBuilder()
		def q = Person.where {
			lastName == "Ali"
				}
		def l = q.list(max:1,offset:0,sort:'birthDate',order:'desc')
		def q2 = Person.where {
			birthDate > l[0].birthDate
		}
		def map4 = [fourWhere: q2.each { str <<  "$it.firstName $it.lastName $it.birthDate  \n"	}]
		render(view: 'fourWhere', model:map4)
		
		
	}
	
	def fourCriteria()
	{
		
		def str = new StringBuilder()
		def query = Person.createCriteria()
		def result = query.get {
		projections {
		sqlProjection("max(birth_date) as maxDob", ['maxDob'],[DATE])
		}
		eq('lastName','Ali')
		}
		def q = Person.createCriteria()
		def r = q.list {
		gt('birthDate',result)
		}
		
		
		def map4 = [ fourCriteria: r.each { str <<  "$it.firstName $it.lastName $it.birthDate  \n"	} ]
		render(view: "fourCriteria", model: map4)
		
		
	}
	
//*****************************************************************************************************	
// ***************88Question  five****************************88
	def fiveFind()
	{
		def str5 = new StringBuilder()
		def query = Person.findAllByLastName('Nazzal',[sort: 'birthDate',order:'asc'])
		def result = Person.findAllByBirthDateLessThan(query[0].birthDate, [sort: 'firstName',order:'asc',max:2, offset:0])
		def map5 = [fiveFind:  result.each { str5 <<  "$it.firstName $it.lastName $it.birthDate  \n"	} ]
		render(view: 'fiveFind', model: map5)
		
	
		
	}
	
	def fiveWhere()
	{
		def str5 = new StringBuilder()
		def firstQuery = Person.where { lastName == "Nazzal" }
		def resultOne = firstQuery.list(offset:0, max:1,sort:'birthDate',order:'asc')

		def secondQuery = Person.where {
			birthDate < resultOne[0].birthDate
		}
		def resultTow = secondQuery.list(offset:0,sort:'birthDate', max:2,order:'asc')
		def map = [fiveWhere: resultTow.each { str5 <<  "\n $it.firstName \n $it.lastName $it.birthDate  \n"	}]
		render(view: 'fiveWhere', model: map)
		
		
	}
	
	def fiveCriteria()
	{
		def queryOne = Person.createCriteria()
		def result1 = queryOne.list{
			eq('lastName', 'Nazzal')
			order('birthDate','asc')
			
		}
		def query2 = Person.createCriteria()
		def result2 = query2.list {
			lt('birthDate', result1[0].birthDate)
			order('firstName','asc')
			
			
			
		}
		def map5 = [fiveCriteria: result2]
		render(view:'fiveCriteria',model: map5)
		
		
		
		
	}	

}	
